<%@ page import="java.sql.*, javax.sql.*, javax.naming.*" %>
<%@page import="support.*, java.util.*" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Confirmation</title>
</head>
<body>
    <!--  Book keeping -->
    <%
    String insert = request.getParameter("Insert");
    String username = (String)request.getUserPrincipal().getName();
    int studentID = 0;
    %>
    
    <%
    // Connect to ConnectionPool
    Connection conn = null;
    try{
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/CS135");
        conn = ds.getConnection();
    }finally{
    }
    Statement stmt = conn.createStatement(); // local variable to store the newly generated studentID
    
    // student id
    ResultSet rset;
    
    // checks if account already has an application
    ResultSet applied = stmt.executeQuery("SELECT student FROM applicants WHERE username=\'" + username + "\'");
    if(applied.next()){
        studentID = applied.getInt(1);
    }
    
    // checks if they have just inserted an application or already have an application
    if( insert != null && !insert.equals("") && studentID == 0){
    out.print("Congratulations! Application was successfully submitted!<br>");
    
    // insertion flag
    if(session.getAttribute("applied") != null && session.getAttribute("applied").equals("true")){
      //Store Application into Database 
          
      // Insert student into table
      PreparedStatement pstmt = conn.prepareStatement("INSERT INTO students (first_name, middle_name, last_name, specialization) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
      // set first name
      pstmt.setString(1, (String)session.getAttribute("first"));
      // set middle name
      pstmt.setString(2, (String)session.getAttribute("middle"));
      // set last name
      pstmt.setString(3, (String)session.getAttribute("last"));
      // set specialization
      pstmt.setString(4, (String)session.getAttribute("specialization"));
      // update
      pstmt.executeUpdate();
    
      // get student ID
      stmt = conn.createStatement();
      rset = pstmt.getGeneratedKeys();
      // gets the last inserted item
      if(rset.next()){
        studentID = rset.getInt(1);
      }
    
      // Insert student's address
      pstmt = conn.prepareStatement(
            "INSERT INTO address (country_citizenship, country_residence, street, city, state, zip, tel_code, area_code, num, domestic, student) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      // sets country of citizenship
      pstmt.setString(1, (String)session.getAttribute("citizen"));
      // sets country of residence
      pstmt.setString(2, (String)session.getAttribute("residence"));
      // sets the street address
      pstmt.setString(3, (String)session.getAttribute("address"));
      // sets the city
      pstmt.setString(4, (String)session.getAttribute("city"));
      // sets the state (optional)
      pstmt.setString(5, (String)session.getAttribute("state"));
      // sets the zip code as a number
      pstmt.setInt(6, Integer.parseInt((String)session.getAttribute("zip")));
      // sets telecode (String because it could have space)
      pstmt.setString(7,(String)session.getAttribute("telecode"));
      // sets area code as number
      pstmt.setInt(8, Integer.parseInt((String)session.getAttribute("area")));
      // sets phone number as a number
      pstmt.setInt(9, Integer.parseInt((String)session.getAttribute("number")));
      // sets residency (domestic or non)
      pstmt.setString(10, (String)session.getAttribute("residency"));
      // sets refernce to student
      pstmt.setInt(11, studentID);
      // update
      pstmt.executeUpdate();
    
      // add the current Degree hashed set to the vector of degrees
      LinkedHashSet degrees = (LinkedHashSet)session.getAttribute("degrees");
      // LinkedHashSet to hold the current degree
      LinkedHashSet currentDegree;
  
      // create iterator to go through all the degrees
      Iterator it = degrees.iterator();
      while(it.hasNext()){
        // get a degree from degrees
        currentDegree = (LinkedHashSet)it.next();
        // go through all the fields of the current degree
        Iterator currentIt = currentDegree.iterator();
        // Insert student's degrees
        pstmt = conn.prepareStatement(
                "INSERT INTO degrees (loc, university,  discipline, grad_month, grad_year, gpa, title, student) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        // sets loc
        pstmt.setString(1, (String)currentIt.next());
        // sets university
        pstmt.setString(2, (String)currentIt.next());
        // sets discipline
        pstmt.setString(3, (String)currentIt.next());
        // sets graduation month
        pstmt.setString(4, (String)currentIt.next());
        // sets graduation year
        pstmt.setInt(5, Integer.parseInt((String)currentIt.next()));
        // sets gpa
        pstmt.setFloat(6, Float.parseFloat((String)currentIt.next()));
        // sets title
        pstmt.setString(7, (String)currentIt.next());
        // matches degree to its student
        pstmt.setInt(8, studentID);
        // update
        pstmt.executeUpdate();
        
        // set the account to point ot this application
        pstmt = conn.prepareStatement(
                "UPDATE applicants SET student = ? WHERE applicants.username = ?");
        // sets loc
        pstmt.setInt(1, studentID);
        // sets university
        pstmt.setString(2, username);
        // update
        pstmt.executeUpdate();
    }
   

    //System.out.println(rset.getString(1));
    // commit changes
    conn.commit();
    
    // Close all of the connections 
    stmt.close();
    rset.close();
    pstmt.close();
    conn.close(); 
    
    // insertion is set, change flag
    session.setAttribute("applied", "false");
    }
    
    //  Display automatically generated ID# 
    out.print("Your Application ID number is: " +  studentID); 

    }else if(studentID != 0){
        out.print("Failure: Application was already submitted");
    }else{
        out.print("Failure: No Applicaiton was created");
    }
    %>
    <!-- Link to Analytics -->
    <br><a href="/Application_Submission/account.do">Back to my account</a>
</body>
</html>